use std::path::PathBuf;
use chrono::{DateTime, Utc};
use rusqlite::{params, Connection};
use uuid::Uuid;

use crate::db_logger;

// 用户数据结构
#[derive(Debug, serde::Serialize, serde::Deserialize, Clone)]
pub struct User {
    pub id: String,
    pub username: String,
    pub avatar: Option<String>,
    pub phone: Option<String>,
    pub created_at: String,
    pub updated_at: String,
}

// 事件数据结构
#[derive(Debug, serde::Serialize, serde::Deserialize, Clone)]
pub struct Event {
    pub id: String,
    pub user_id: String,
    pub title: String,
    pub description: Option<String>,
    pub event_type: String,
    pub date: String,
    pub image_url: Option<String>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

// 用户设置数据结构
#[derive(Debug, serde::Serialize, serde::Deserialize, Clone)]
pub struct UserSetting {
    pub id: String,
    pub user_id: String,
    pub setting_key: String,
    pub setting_value: String,
    pub created_at: String,
    pub updated_at: String,
}

// 用户日志数据结构
#[derive(Debug, serde::Serialize, serde::Deserialize, Clone)]
pub struct UserLog {
    pub id: String,
    pub user_id: String,
    pub action: String,
    pub description: Option<String>,
    pub created_at: String,
}

// 文件记录数据结构
#[derive(Debug, serde::Serialize, serde::Deserialize, Clone)]
pub struct FileRecord {
    pub id: String,
    pub user_id: String,
    pub file_url: String,
    pub file_hash: String,
    pub filename: String,
    pub file_size: u64,
    pub created_at: String,
}

// 应用数据存储
pub struct AppState {
    pub db: Connection,
}

impl AppState {
    // 创建新的应用状态
    pub fn new(data_dir: PathBuf) -> Result<Self, rusqlite::Error> {
        let db_path = data_dir.join("life_app.db");
        let db = Connection::open(db_path)?;
        let mut state = Self { db };
        state.init_database();
        Ok(state)
    }
    
    // 降级创建应用状态的方法，用于在主要方法失败时提供回退
    pub fn new_with_fallback(data_dir: PathBuf) -> Self {
        let _db_path = data_dir.join("fallback_life_app.db");
        let db = Connection::open_in_memory().expect("Failed to open in-memory database");
        let mut state = Self { db };
        
        // 尝试初始化数据库，但不panic
        if let Err(e) = state.safe_init_database() {
            eprintln!("初始化内存数据库失败: {}", e);
        }
        
        state
    }
    
    // 安全地初始化数据库，返回错误而不是panic
    pub fn safe_init_database(&mut self) -> Result<(), rusqlite::Error> {
        // 这里可以复制init_database的代码，但使用?而不是expect
        // 为简化，我们只初始化必要的表
        let sql = "CREATE TABLE IF NOT EXISTS t_event (id TEXT PRIMARY KEY, user_id TEXT NOT NULL, title TEXT NOT NULL)";
        self.db.execute(sql, [])?;
        Ok(())
    }
    
    // 初始化数据库表
    pub fn init_database(&mut self) {
        // 创建用户表
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "CREATE TABLE IF NOT EXISTS t_user (
                id TEXT PRIMARY KEY,
                username TEXT NOT NULL,
                avatar TEXT,
                phone TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )",
            &[],
        ) {
            eprintln!("创建用户表失败: {}", e);
        }
        
        // 确保phone列存在（兼容旧版本数据库）
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "ALTER TABLE t_user ADD COLUMN IF NOT EXISTS phone TEXT",
            &[],
        ) {
            eprintln!("添加phone列失败: {}", e);
        }

        // 创建事件表
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "CREATE TABLE IF NOT EXISTS t_event (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                title TEXT NOT NULL,
                description TEXT,
                event_type TEXT NOT NULL,
                date TEXT NOT NULL,
                image_url TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )",
            &[],
        ) {
            eprintln!("创建事件表失败: {}", e);
        }

        // 创建用户设置表
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "CREATE TABLE IF NOT EXISTS t_user_setting (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                setting_key TEXT NOT NULL,
                setting_value TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                UNIQUE(user_id, setting_key)
            )",
            &[],
        ) {
            eprintln!("创建用户设置表失败: {}", e);
        }

        // 创建用户日志表
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "CREATE TABLE IF NOT EXISTS t_user_log (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                action TEXT NOT NULL,
                description TEXT,
                created_at TEXT NOT NULL
            )",
            &[],
        ) {
            eprintln!("创建用户日志表失败: {}", e);
        }

        // 创建文件记录表
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "CREATE TABLE IF NOT EXISTS t_file_record (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                file_url TEXT NOT NULL,
                file_hash TEXT NOT NULL,
                filename TEXT NOT NULL,
                file_size INTEGER NOT NULL,
                created_at TEXT NOT NULL
            )",
            &[],
        ) {
            eprintln!("创建文件记录表失败: {}", e);
        }

        // 确保默认用户存在
        if let Err(e) = self.ensure_default_user() {
            eprintln!("确保默认用户存在失败: {}", e);
        }
    }

    // 确保默认用户存在
    pub fn ensure_default_user(&mut self) -> Result<(), rusqlite::Error> {
        let default_user_id = "default_user";
        let count: i64 = match db_logger::query_scalar(
            &self.db,
            "SELECT COUNT(*) FROM t_user WHERE id = ?",
            params![default_user_id],
        ) {
            Ok(count) => count,
            Err(e) => {
                eprintln!("查询默认用户失败: {}", e);
                return Err(e);
            },
        };

        if count == 0 {
            let now = Utc::now();
            let now_str = now.to_rfc3339();
            let user = User {
                id: default_user_id.to_string(),
                username: "默认用户".to_string(),
                avatar: None::<String>,
                phone: None::<String>,
                created_at: now_str.clone(),
                updated_at: now_str,
            };

            match db_logger::execute_non_query(
                &self.db,
                "INSERT INTO t_user (id, username, avatar, phone, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)",
                params![user.id, user.username, user.avatar, user.phone, user.created_at, user.updated_at],
            ) {
                Ok(_) => Ok(()),
                Err(e) => {
                    eprintln!("创建默认用户失败: {}", e);
                    Err(e)
                },
            }
        } else {
            Ok(())
        }
    }

    // 获取所有事件
    pub fn get_all_events(&self, user_id: &str) -> Vec<Event> {
        let sql = "SELECT id, user_id, title, description, event_type, date, image_url, created_at, updated_at 
                   FROM t_event WHERE user_id = ? ORDER BY date DESC";
        match db_logger::query_map(
            &self.db,
            sql,
            params![user_id],
            |row| {
                let created_at_str: String = row.get(7)?;
                let updated_at_str: String = row.get(8)?;
                
                let created_at = DateTime::parse_from_rfc3339(&created_at_str)
                    .map_err(|_| rusqlite::Error::InvalidColumnType(7, "DateTime".to_string(), rusqlite::types::Type::Text))?
                    .with_timezone(&Utc);
                
                let updated_at = DateTime::parse_from_rfc3339(&updated_at_str)
                    .map_err(|_| rusqlite::Error::InvalidColumnType(8, "DateTime".to_string(), rusqlite::types::Type::Text))?
                    .with_timezone(&Utc);
                
                Ok(Event {
                    id: row.get(0)?,
                    user_id: row.get(1)?,
                    title: row.get(2)?,
                    description: row.get(3)?,
                    event_type: row.get(4)?,
                    date: row.get(5)?,
                    image_url: row.get(6)?,
                    created_at,
                    updated_at,
                })
            },
        ) {
            Ok(events) => events.into_iter().collect(),
            Err(e) => {
                eprintln!("获取事件列表失败: {}", e);
                Vec::new()
            },
        }
    }

    // 保存或更新事件
    pub fn save_event(&mut self, mut event: Event) -> Result<(), rusqlite::Error> {
        let now = Utc::now();
        event.updated_at = now;
        
        // 检查事件是否已存在
        let count: i64 = match db_logger::query_scalar(
            &self.db,
            "SELECT COUNT(*) FROM t_event WHERE id = ?",
            params![event.id],
        ) {
            Ok(count) => count,
            Err(e) => {
                eprintln!("检查事件是否存在失败: {}", e);
                return Err(e);
            },
        };

        let now_str = now.to_rfc3339();
        if count > 0 {
            // 更新事件
            if let Err(e) = db_logger::execute_non_query(
                &self.db,
                "UPDATE t_event SET title = ?, description = ?, event_type = ?, date = ?, image_url = ?, updated_at = ? WHERE id = ?",
                params![event.title, event.description.as_deref(), event.event_type, event.date, event.image_url.as_deref(), now_str, event.id],
            ) {
                eprintln!("更新事件失败: {}", e);
                return Err(e);
            }
        } else {
            // 创建新事件
            event.created_at = now;
            if let Err(e) = db_logger::execute_non_query(
                &self.db,
                "INSERT INTO t_event (id, user_id, title, description, event_type, date, image_url, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                params![event.id, event.user_id, event.title, event.description.as_deref(), event.event_type, event.date, event.image_url.as_deref(), now_str, now_str],
            ) {
                eprintln!("创建新事件失败: {}", e);
                return Err(e);
            }
        }

        // 记录用户操作日志，但忽略错误
        let _ = self.log_user_action(&event.user_id, "save_event", Some(&format!("保存事件: {}", event.title)));
        
        Ok(())
    }

    // 删除事件
    pub fn delete_event(&mut self, event_id: &str, user_id: &str) -> bool {
        // 先检查事件是否存在
        let count: i64 = match db_logger::query_scalar(
            &self.db,
            "SELECT COUNT(*) FROM t_event WHERE id = ? AND user_id = ?",
            params![event_id, user_id],
        ) {
            Ok(count) => count,
            Err(e) => {
                eprintln!("检查事件是否存在失败: {}", e);
                return false;
            },
        };

        if count == 0 {
            return false;
        }

        // 删除事件
        match db_logger::execute_non_query(
            &self.db,
            "DELETE FROM t_event WHERE id = ? AND user_id = ?",
            params![event_id, user_id],
        ) {
            Ok(_) => {
                // 记录用户操作日志，但忽略错误
                let _ = self.log_user_action(user_id, "delete_event", Some(&format!("删除事件: {}", event_id)));
                true
            },
            Err(e) => {
                eprintln!("删除事件失败: {}", e);
                false
            },
        }
    }

    // 根据ID获取事件
    pub fn get_event_by_id(&self, event_id: &str, user_id: &str) -> Option<Event> {
        let sql = "SELECT id, user_id, title, description, event_type, date, image_url, created_at, updated_at 
                   FROM t_event WHERE id = ? AND user_id = ?";
        match db_logger::query_row(
            &self.db,
            sql,
            params![event_id, user_id],
            |row| {
                let created_at_str: String = row.get(7)?;
                let updated_at_str: String = row.get(8)?;
                
                let created_at = DateTime::parse_from_rfc3339(&created_at_str)
                    .map_err(|_| rusqlite::Error::InvalidColumnType(7, "DateTime".to_string(), rusqlite::types::Type::Text))?
                    .with_timezone(&Utc);
                
                let updated_at = DateTime::parse_from_rfc3339(&updated_at_str)
                    .map_err(|_| rusqlite::Error::InvalidColumnType(8, "DateTime".to_string(), rusqlite::types::Type::Text))?
                    .with_timezone(&Utc);
                
                Ok(Event {
                    id: row.get(0)?,
                    user_id: row.get(1)?,
                    title: row.get(2)?,
                    description: row.get(3)?,
                    event_type: row.get(4)?,
                    date: row.get(5)?,
                    image_url: row.get(6)?,
                    created_at,
                    updated_at,
                })
            },
        ) {
            Ok(event) => Some(event),
            Err(e) => {
                eprintln!("获取事件详情失败: {}", e);
                None
            },
        }
    }

    // 记录用户操作日志
    pub fn log_user_action(&self, user_id: &str, action: &str, description: Option<&str>) -> Result<(), rusqlite::Error> {
        let log_id = Uuid::new_v4().to_string();
        let now = Utc::now();
        let now_str = now.to_rfc3339();
        
        match db_logger::execute_non_query(
            &self.db,
            "INSERT INTO t_user_log (id, user_id, action, description, created_at) VALUES (?, ?, ?, ?, ?)",
            params![log_id, user_id, action, description, now_str],
        ) {
            Ok(_) => Ok(()),
            Err(e) => {
                eprintln!("记录用户操作日志失败: {}", e);
                Err(e)
            },
        }
    }

    // 获取或创建用户设置
    pub fn get_or_create_user_setting(&mut self, user_id: &str, key: &str, default_value: &str) -> String {
        let sql = "SELECT setting_value FROM t_user_setting WHERE user_id = ? AND setting_key = ?";
        
        match db_logger::query_row(
            &self.db,
            sql,
            &[&user_id, &key],
            |row| row.get::<_, String>(0)
        ) {
            Ok(value) => value,
            Err(_) => {
                // 如果不存在，创建新的设置
                self.update_user_setting(user_id, key, default_value);
                default_value.to_string()
            }
        }
    }
    
    // 更新用户设置
    pub fn update_user_setting(&mut self, user_id: &str, key: &str, value: &str) {
        let now = Utc::now();
        let now_str = now.to_rfc3339();
        let setting_id = Uuid::new_v4().to_string();

        // 尝试更新，如果不存在则插入
        let sql = "INSERT OR REPLACE INTO t_user_setting (id, user_id, setting_key, setting_value, created_at, updated_at) 
                   VALUES (?, ?, ?, ?, COALESCE((SELECT created_at FROM t_user_setting WHERE user_id = ? AND setting_key = ?), ?), ?)";
        
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            sql,
            &[&setting_id, &user_id, &key, &value, &user_id, &key, &now_str, &now_str],
        ) {
            eprintln!("更新用户设置失败: {}", e);
        }
    }
    
    // 通过哈希获取文件URL
    pub fn get_file_by_hash(&self, user_id: &str, hash_hex: &str) -> Option<String> {
        let sql = "SELECT file_url FROM t_file_record WHERE user_id = ? AND file_hash = ?";
        
        match db_logger::query_row(
            &self.db,
            sql,
            &[&user_id, &hash_hex],
            |row| row.get::<_, String>(0)
        ) {
            Ok(file_url) => Some(file_url),
            Err(_) => None,
        }
    }

    // 更新用户信息（包含手机号）
    pub fn update_user_info(&mut self, user_id: &str, username: &str, avatar: Option<&str>, phone: Option<&str>) -> Result<(), rusqlite::Error> {
        let now = Utc::now();
        let now_str = now.to_rfc3339();
        
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "UPDATE t_user SET username = ?, avatar = ?, phone = ?, updated_at = ? WHERE id = ?",
            params![username, avatar, phone, now_str, user_id],
        ) {
            eprintln!("更新用户信息失败: {}", e);
            return Err(e);
        }
        
        // 记录用户操作日志，但忽略错误
        let _ = self.log_user_action(user_id, "update_user_info", Some(&format!("更新用户名: {}", username)));
        
        Ok(())
    }

    // 获取用户信息
    pub fn get_user_info(&self, user_id: &str) -> Option<User> {
        match db_logger::query_row(
            &self.db,
            "SELECT id, username, avatar, phone, created_at, updated_at FROM t_user WHERE id = ?",
            params![user_id],
            |row| {
                Ok(User {
                    id: row.get(0)?,
                    username: row.get(1)?,
                    avatar: row.get(2)?,
                    phone: row.get(3)?,
                    created_at: row.get(4)?,
                    updated_at: row.get(5)?,
                })
            },
        ) {
            Ok(user) => Some(user),
            Err(e) => {
                eprintln!("获取用户信息失败: {}", e);
                None
            },
        }
    }

    // 保存文件记录
    pub fn save_file_record(&mut self, user_id: &str, file_url: &str, file_hash: &str, filename: &str, file_size: u64) -> Result<(), rusqlite::Error> {
        let file_id = Uuid::new_v4().to_string();
        let now = Utc::now();
        let now_str = now.to_rfc3339();
        
        if let Err(e) = db_logger::execute_non_query(
            &self.db,
            "INSERT INTO t_file_record (id, user_id, file_url, file_hash, filename, file_size, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
            params![file_id, user_id, file_url, file_hash, filename, file_size, now_str],
        ) {
            eprintln!("保存文件记录失败: {}", e);
            return Err(e);
        }
        
        Ok(())
    }
}